package com.autonavi.yunda.yunji.core.engine.handler;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.sql.parser.Token;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.wall.WallCheckResult;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallProvider;
import com.alibaba.druid.wall.spi.MySqlWallProvider;
import com.alibaba.druid.wall.spi.PGWallProvider;
import com.autonavi.yunda.yunji.common.exception.AmapException;
import com.autonavi.yunda.yunji.common.exception.AmapExceptionCode;
import com.autonavi.yunda.yunji.common.utils.JsonUtils;
import com.autonavi.yunda.yunji.common.utils.SpringUtil;
import com.autonavi.yunda.yunji.core.engine.handler.vo.SqlOptionVO;
import com.autonavi.yunda.yunji.core.enums.DatasourceType;
import com.autonavi.yunda.yunji.core.enums.SqlDebugType;
import com.autonavi.yunda.yunji.core.enums.SqlResultType;
import com.autonavi.yunda.yunji.core.service.dbfactory.DbFactoryService;
import com.autonavi.yunda.yunji.core.utils.CamelSnakeTransfer;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.groovy.util.Maps;
import org.jetbrains.annotations.NotNull;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.CollectionUtils;

import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @author cizhong.wcs
 */
@Slf4j
public class SqlHandler {
    private static final LoadingCache<String, DbType> dbTypeCache = CacheBuilder
            .newBuilder()
            .maximumSize(1000)
            .expireAfterWrite(30, TimeUnit.MINUTES)
            .build(new CacheLoader<>() {
                @NotNull
                @Override
                public DbType load(String s) {
                    log.info("load db type from db, key:{}", s);
                    return getDbTypeFromDb(s);
                }
            });

    public static List<Map<String, Object>> sql(String dbName, String sql) {
        // 判断该sql是否符合数据库配置的操作权限（增删改查）
        checkValid(dbName, sql);
        JdbcTemplate jdbcTemplate = getJdbcTemplate(dbName);
        return executeSql(jdbcTemplate, sql, dbName);
    }

    public static Object sql(String dbName, String sql, Map<String, Object> options) {
        SqlOptionVO optionVO = JsonUtils.from(JsonUtils.toString(options), SqlOptionVO.class);
        if (optionVO.sqlDebugType != null && optionVO.sqlDebugType.isDebug()) {
            return sqlDebug(optionVO.sqlDebugType, dbName, sql);
        }
        List<Map<String, Object>> result = sql(dbName, sql);
        if (optionVO.camel) {
            result = CamelSnakeTransfer.transformSnakeToCamel(result);
        }
        if (SqlResultType.MAP.equals(optionVO.sqlResultType)) {
            return result.size() == 0 ? null : result.get(0);
        }
        return result;
    }

    /**
     * 获取jdbcTemplate
     *
     * @param dbName dbname
     * @return jdbcTemplate
     */
    private static JdbcTemplate getJdbcTemplate(String dbName) {
        JdbcTemplate jdbcTemplate;
        DbFactoryService dbFactoryService = SpringUtil.getBean(DbFactoryService.class);
        try {
            jdbcTemplate = dbFactoryService.getInstance(dbName);
        } catch (Exception e) {
            log.error("从dbFactoryService获取数据源失败 dbName:{}", dbName);
            throw new AmapException(AmapExceptionCode.COMMON_SERVER_ERROR, "获取数据源失败");
        }
        return jdbcTemplate;
    }


    private static void checkValid(String dbName, String sql) {
        DbFactoryService service = SpringUtil.getBean(DbFactoryService.class);
        if ("cloudmap-yunji-engine".equals(service.getAppName())) {
            return;
        }
        Pair<DatasourceType, String> dataSourceConfig = service.findDataSourceConfigByName(dbName);
        Set<String> operators = service.getDatasourceOperators(dataSourceConfig.getRight());
        WallConfig wallConfig = new WallConfig();
        wallConfig.setInsertAllow(operators.contains("insert"));
        wallConfig.setSelectAllow(operators.contains("select"));
        wallConfig.setUpdateAllow(operators.contains("update"));
        wallConfig.setDeleteAllow(operators.contains("delete"));
        wallConfig.setCreateTableAllow(operators.contains("createTable"));
        wallConfig.setDropTableAllow(operators.contains("dropTable"));
        wallConfig.setAlterTableAllow(operators.contains("alterTable"));
        wallConfig.setTruncateAllow(operators.contains("truncate"));
        WallProvider provider = dataSourceConfig.getLeft().equals(DatasourceType.PGSQL) ?
                new PGWallProvider(wallConfig) : new MySqlWallProvider(wallConfig);
        WallCheckResult checkResult = provider.check(sql);
        if (!checkResult.getViolations().isEmpty()) {
            throw new AmapException(AmapExceptionCode.COMMON_ILLEGAL_PARAM,
                    "sql异常:" + checkResult.getViolations().get(0).getMessage());
        }
    }

    public static List<Map<String, Object>> executeSql(JdbcTemplate jdbcTemplate, String sql, String dbName) {
        DbType dbType = getDbType(dbName);
        // 解析sql
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
        Token token = parser.getLexer().token();
        log.info("token = {} executeSql start", token);
        List<Map<String, Object>> results = new ArrayList<>();
        Map<String, Object> map = new HashMap<>(8);
        switch (token) {
            case SELECT:
                results = jdbcTemplate.queryForList(sql);
                log.info("sql result size = {}", results.size());
                break;
            case INSERT:
                KeyHolder keyHolder = new GeneratedKeyHolder();
                new NamedParameterJdbcTemplate(jdbcTemplate).update(sql, EmptySqlParameterSource.INSTANCE, keyHolder, new String[]{"id"});
                List<Map<String, Object>> keyList = keyHolder.getKeyList();
                log.info("keyList=>{}", JsonUtils.toString(keyList));
                if (!CollectionUtils.isEmpty(keyList)) {
                    List<Map<String, Object>> idList = keyList.stream().map(rowMap -> Map.of("id", rowMap.values().iterator().next()))
                            .collect(Collectors.toList());
                    results.addAll(idList);
                } else {
                    map.put("result", "success");
                    results.add(map);
                }
                break;
            case UPDATE:
            case DELETE:
                Integer rowNum = jdbcTemplate.update(sql);
                map.put("rowNum", rowNum);
                results.add(map);
                break;
            default:
                jdbcTemplate.execute(sql);
                map.put("result", "success");
                results.add(map);
        }
        return results;
    }

    /**
     * debug前获取完整sql&影响行数
     *
     * @param dbName 数据库名称
     * @param sql    sql
     * @return 完整sql+影响行数
     */
    public static Object sqlDebug(SqlDebugType debug, String dbName, String sql) {
        log.info("dbName : {},sql : {}", dbName, sql);
        Map<String, Object> finalMap = showFinalSql(dbName, sql);
        if (SqlDebugType.SHOW_ALL.equals(debug)) {
            Map<String, Object> rowsMap = showEffectRows(dbName, sql);
            finalMap.putAll(rowsMap);
        }
        return finalMap;
    }

    /**
     * debug前获取完整sql
     *
     * @param dbName 数据库名称
     * @param sql    sql
     * @return 完整sql
     */
    public static Map<String, Object> showFinalSql(String dbName, String sql) {
        Map<String, Object> map = new HashMap<>(8);
        map.put("sql", sql);
        return map;
    }

    /**
     * debug前获取sql影响行数-现阶段仅update&delete语句用
     *
     * @param dbName 数据库名称
     * @param sql    sql
     * @return 影响行数
     */
    public static Map<String, Object> showEffectRows(String dbName, String sql) {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, getDbType(dbName));
        Token token = parser.getLexer().token();
        checkValid(dbName, sql);
        JdbcTemplate jdbcTemplate = getJdbcTemplate(dbName);
        if (Token.UPDATE.equals(token) || Token.DELETE.equals(token)) {
            SQLStatement stmt = SQLUtils.parseStatements(sql, getDbType(dbName)).get(0);
            SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(getDbType(dbName));
            stmt.accept(visitor);
            int situation = sql.toUpperCase().indexOf("WHERE");
            String tableName = visitor.getTables().keySet().toArray()[0].toString();
            return jdbcTemplate.queryForList("select count(*) as rowNum from " + tableName + " " + (situation != -1 ? sql.substring(situation) : "")).get(0);
        }
        return Maps.of("rowNum", -1);
    }

    private static DbType getDbType(String dbName) {
        try {
            return dbTypeCache.get(dbName);
        } catch (ExecutionException e) {
            log.warn("getDbType from cache error", e);
            return getDbTypeFromDb(dbName);
        }
    }

    /**
     * 根据dbName获取当前数据库类型
     *
     * @param dbName dbName
     * @return dbName匹配的DbType
     */
    private static DbType getDbTypeFromDb(String dbName) {
        DbFactoryService service = SpringUtil.getBean(DbFactoryService.class);
        Pair<DatasourceType, String> dataSourceConfig = service.findDataSourceConfigByName(dbName);
        boolean isPgsqlFlag = DatasourceType.PGSQL.equals(dataSourceConfig.getLeft());
        return isPgsqlFlag ? DbType.postgresql : DbType.mysql;
    }
}
